﻿using OfficeOpenXml;
using OfficeOpenXml.Style;
using SqlSugar.Extensions;
using System.Data;
using System.Text.RegularExpressions;
using KalevaAalto.DataPart.Excel.Models;


namespace KalevaAalto
{
    public static partial class Static
    {



        /// <summary>
        /// 检查单元格是否为空
        /// </summary>
        /// <param name="worksheet">工作表</param>
        /// <param name="cellPos">单元格</param>
        /// <returns>返回单元格是否为空</returns>
        public static bool IsCellEmpty(this ExcelWorksheet worksheet, CellPos cellPos)
        {


            object? value = worksheet.GetValue((int)cellPos.Row, (int)cellPos.Column);
            if (value is null || string.IsNullOrEmpty(value.ToString()))
            {
                return true;
            }

            return false;

        }


        /// <summary>
        /// 检查单元格是否为空
        /// </summary>
        /// <param name="worksheet">工作表</param>
        /// <param name="row">行号</param>
        /// <param name="column">列号</param>
        /// <returns>返回单元格是否为空</returns>
        public static bool IsCellEmpty(this ExcelWorksheet worksheet, int row, int column)
        {
            return worksheet.IsCellEmpty(new CellPos(row, column));
        }




        /// <summary>
        /// 获取工作表的起始单元格坐标
        /// </summary>
        /// <param name="worksheet">工作表</param>
        /// <returns>返回工作表的起始单元格坐标</returns>
        public static CellPos GetStartCellPos(this ExcelWorksheet worksheet)
        {
            for (int row = 1; row <= 500; row++)
            {
                for (int column = 1; column <= 500; column++)
                {
                    if (!worksheet.IsCellEmpty(row, column))
                    {
                        return new CellPos(row, column);
                    }
                }
            }


            throw new NotImplementedException();
        }

        /// <summary>
        /// 获取工作表的起始单元格坐标
        /// </summary>
        /// <param name="worksheet">工作表</param>
        /// <param name="startString">起始点标志字符串</param>
        /// <returns>返回工作表的起始单元格坐标</returns>
        public static CellPos GetStartCellPos(this ExcelWorksheet worksheet, string startString)
        {
            if (string.IsNullOrEmpty(startString))
            {
                return worksheet.GetStartCellPos();
            }

            for (int row = 1; row <= 500; row++)
            {
                for (int column = 1; column <= 500; column++)
                {
                    if (!worksheet.IsCellEmpty(row, column) && worksheet.GetValue<string>(row, column) == startString)
                    {
                        return new CellPos(row, column);
                    }
                }
            }


            throw new NotImplementedException();
        }



        public static DataTable GetTableFromSheet(string file_name, string sheet_name)
        {

            var package = new ExcelPackage(new FileInfo(file_name));

            var worksheet = package.Workbook.Worksheets[sheet_name];
            return worksheet.ToDataTable();
        }




        public static HashSet<string> WorksheetNames(this ExcelPackage package)
        {
            HashSet<string> worksheetNames = new HashSet<string>();

            foreach (ExcelWorksheet worksheet in package.Workbook.Worksheets)
            {
                worksheetNames.Add(worksheet.Name);
            }

            return worksheetNames;
        }


        public static HashSet<string> WorksheetNames(string file_name)
        {
            ExcelPackage package = new ExcelPackage(new FileInfo(file_name));
            return package.WorksheetNames();
        }


        public static bool IsExists(this ExcelPackage package, string worksheet_name)
        {
            bool rs = false;
            foreach (ExcelWorksheet worksheet in package.Workbook.Worksheets)
            {
                if (worksheet.Name == worksheet_name) return true;
            }

            return rs;
        }


        public static bool IsExists(this ExcelPackage package, string[] worksheet_names)
        {
            bool rs = true;
            HashSet<string> strings = new HashSet<string>();
            foreach (ExcelWorksheet worksheet in package.Workbook.Worksheets) strings.Add(worksheet.Name);

            foreach (string worksheet_name in worksheet_names)
            {
                if (!strings.Contains(worksheet_name))
                {
                    return false;
                }
            }


            return rs;
        }



        #region 单元格取数
        public static string GetStringFromCell(this ExcelWorksheet worksheet, int i, int j)
        {
            object cell = worksheet.GetValue(i, j);
            if (cell is null) return string.Empty;
            else return cell.ToString() ?? string.Empty;
        }

        public static int GetIntFromCell(this ExcelWorksheet worksheet, int i, int j)
        {
            object cell = worksheet.GetValue(i, j);
            if (cell is null) return 0;
            else return cell.ObjToInt();
        }


        public static decimal GetDecimalFromCell(this ExcelWorksheet worksheet, int i, int j)
        {
            object cell = worksheet.GetValue(i, j);
            if (cell is null) return 0m;
            else return cell.ObjToDecimal();
        }

        public static double GetDoubleFromCell(this ExcelWorksheet worksheet, int i, int j)
        {
            object cell = worksheet.GetValue(i, j);
            if (cell is null) return 0;
            else return Convert.ToDouble(cell);
        }


        public static DateTime GetDateTimeFromCell(this ExcelWorksheet worksheet, int i, int j)
        {
            object cell = worksheet.GetValue(i, j);
            if (cell is null) return DateTime.MinValue;
            else return cell.ObjToDate();
        }


        #endregion



        private readonly static Regex regexEndNumber = new Regex(@"^(.*?)(\d*)$");

        /// <summary>
        /// 将单元格区域的数据转化为数据表
        /// </summary>
        /// <param name="rng">单元格区域</param>
        /// <returns>返回单元格区域的数据的数据表</returns>
        public static DataTable ExcelRangeToDataTable(this ExcelRange rng)
        {
            DataTable table = new DataTable();
            int columnsCount = rng.End.Column - rng.Start.Column + 1;
            int rowsCount = rng.End.Row - rng.Start.Row + 1;



            //录入字段列表
            for (int columnNumber = 1; columnNumber <= columnsCount; columnNumber++)
            {
                string columnName = rng[1, columnNumber].Value.ToString()!;
                while (table.IsExistsColumn(columnName))
                {
                    Match match = regexEndNumber.Match(columnName);
                    int matchNumber = match.Groups[2].Value.StringToInt();
                    columnName = match.Groups[1].Value + (matchNumber + 1);
                }

                table.Columns.Add(columnName, typeof(object));
            }



            //录入数据
            for (int rowNumber = 2; rowNumber <= rowsCount; rowNumber++)
            {
                DataRow row = table.Rows.Add();
                for (int columnNumber = 1; columnNumber <= columnsCount; columnNumber++)
                {
                    row[columnNumber - 1] = rng[rowNumber, columnNumber].GetCellValue<object>();
                }

            }


            return table;
        }



        public static ExcelWorksheet UpToWorkSheet(this ExcelPackage package, DataTable table)
        {
            ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(table.TableName);
            if (table.Columns.Count == 0) return worksheet;
            int Number;
            ExcelRange rng;




            //录入字段
            Number = 1;
            foreach (DataColumn column in table.Columns)
            {
                worksheet.Cells[1, Number].Value = column.ColumnName;
                Number++;
            }




            //录入数据
            Number = 2;
            foreach (DataRow row in table.Rows)
            {
                for (int j = 0; j < table.Columns.Count; j++)
                {
                    worksheet.Cells[Number, j + 1].Value = row[j]; // 列索引 + 1，因为EPPlus的列索引从1开始  
                }
                Number++;
            }


            //设置字段格式
            rng = worksheet.Cells[1, 1, 1, table.Columns.Count];
            rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;    //水平居中
            rng.Style.VerticalAlignment = ExcelVerticalAlignment.Center;    //垂直居中
            rng.Style.Font.Bold = true;
            rng.AutoFilter = true;




            //设置数据格式
            if (table.Rows.Count > 0)
            {
                Number = 1;
                foreach (DataColumn column in table.Columns)
                {
                    rng = worksheet.Cells[2, Number, table.Rows.Count + 1, Number];
                    rng.Style.VerticalAlignment = ExcelVerticalAlignment.Center;    //垂直居中
                    Type type = column.DataType;
                    if (type == typeof(int))
                    {
                        rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
                        rng.Style.Numberformat.Format = @"#,##0";
                    }
                    else if (type == typeof(decimal))
                    {
                        rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
                        rng.Style.Numberformat.Format = @"#,##0.0000";
                    }
                    else if (type == typeof(DateTime))
                    {
                        rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                        rng.Style.Numberformat.Format = @"yyyy_MM_dd hh:mm:ss";
                    }
                    else
                    {
                        rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;    //水平居中
                    }
                    worksheet.Column(Number).AutoFit(5, 30); //自适应宽度


                    Number++;
                }
            }



            //全表添加边框
            rng = worksheet.Cells[1, 1, table.Rows.Count + 1, table.Columns.Count];
            worksheet.View.FreezePanes(2, 1);



            return worksheet;
        }


        public static DataTable ToDataTable(this ExcelWorksheet worksheet, string startString = EmptyString)
        {
            CellPos startCellPos = worksheet.GetStartCellPos(startString);

            //定位
            int columnsCount = int.MaxValue;
            for (int columnNumber = startCellPos.Column; columnNumber <= int.MaxValue; columnNumber++)
            {
                if (worksheet.IsCellEmpty(startCellPos.Row, columnNumber))
                {
                    columnsCount = columnNumber - 1;
                    break;
                }
            }

            int rowsCount = int.MaxValue;
            for (int rowsNumber = startCellPos.Row; rowsNumber <= int.MaxValue; rowsNumber++)
            {
                if (worksheet.IsCellEmpty(rowsNumber, startCellPos.Column))
                {
                    rowsCount = rowsNumber - 1;
                    break;
                }
            }
            return worksheet.Cells[(int)startCellPos.Row, (int)startCellPos.Column, rowsCount, columnsCount].ExcelRangeToDataTable();
        }

        public static void FieldsToDate(this DataTable table, string[] field_names)
        {
            foreach (string field_name in field_names)
            {
                if (!table.IsExistsColumn(field_name))
                {
                    continue;
                }
                foreach (DataRow row in table.Rows)
                {
                    var cell = row[field_name];
                    if (cell is null)
                    {
                        continue;
                    }
                    if (cell.GetType() == typeof(int) || cell.GetType() == typeof(double))
                    {
                        row[field_name] = DateTime.FromOADate((double)cell);
                    }
                    else if (cell.GetType() == typeof(string))
                    {
                        string date_str = (string)cell;
                        row[field_name] = DateTime.FromOADate(Convert.ToDouble(date_str));
                    }
                    else row[field_name] = null;


                }
            }
        }


        public static MemoryStream SaveAsMemoryStream(this ExcelPackage package)
        {
            MemoryStream memoryStream = new MemoryStream();
            package.SaveAs(memoryStream);
            memoryStream.Position = 0;
            return memoryStream;
        }
        public static MemoryStream SaveToExcel(this DataTable table)
        {
            // 创建一个新的Excel文件
            ExcelPackage excelPackage = new ExcelPackage();

            // 新建一个名为"MySheet"的工作表
            string work_sheet_name = table.TableName;
            if (work_sheet_name.Length == 0) work_sheet_name = "Sheet1";
            ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets.Add(work_sheet_name);

            //写入表头
            for (int j = 0; j < table.Columns.Count; j++) worksheet.Cells[1, j + 1].Value = table.Columns[j].ColumnName;

            for (int i = 0; i < table.Rows.Count; i++)
            {
                for (int j = 0; j < table.Columns.Count; j++) worksheet.Cells[i + 2, j + 1].Value = table.Rows[i][j];

            }


            return excelPackage.SaveAsMemoryStream();
        }




        public static void SaveToExcel(this DataTable table, string FileName)
        {
            var Stream = table.SaveToExcel();
            using (FileStream fileStream = new FileStream(FileName, FileMode.Create))
            {
                Stream.WriteTo(fileStream);
            }

        }


        public static bool IsEmpty(this ExcelRange rng)
        {
            return rng.Value == null || rng.GetCellValue<string>().Length == 0;
        }




        #region ExcelColumnMatch

        /// <summary>
        /// Excel表单匹配列
        /// </summary>
        public class ExcelColumnMatch
        {
            public HashSet<string> maybeColumnName { get; private set; }
            public string columnName { get; private set; }

            public Type type { get; private set; }

            public ExcelColumnMatch(string[] names, string columnName, Type? type = null)
            {
                maybeColumnName = names.ToHashSet();
                this.columnName = columnName;
                if (type is not null)
                {
                    this.type = type;
                }
                else
                {
                    this.type = typeof(string);
                }

            }
        }

        public static bool Status(this ExcelColumnMatch[] excelColumnMatches)
        {
            HashSet<string> columnNamesCheck = new HashSet<string>();

            foreach (ExcelColumnMatch excelColumnMatch in excelColumnMatches)
            {
                foreach (string columnName in excelColumnMatch.maybeColumnName)
                {
                    if (columnNamesCheck.Contains(columnName))
                    {
                        return false;
                    }
                    else
                    {
                        columnNamesCheck.Add(columnName);
                    }
                }
            }

            return true;
        }



        public static DataTable GetNewDataTable(this ExcelColumnMatch[] excelColumnMatches, string tableName = EmptyString)
        {
            DataTable result = new DataTable(tableName);
            foreach (ExcelColumnMatch excelColumnMatch in excelColumnMatches)
            {
                result.Columns.Add(excelColumnMatch.columnName, excelColumnMatch.type);
            }
            return result;
        }







        public static void ParseTable(this ExcelColumnMatch[] excelColumnMatchs, DataTable excelTable, DataTable table)
        {


            Dictionary<string, int> keyValuePairs = new Dictionary<string, int>();

            //进行字段匹配
            foreach (ExcelColumnMatch excelColumnMatch in excelColumnMatchs)
            {
                for (int i = 0; i < excelTable.Columns.Count; i++)
                {
                    DataColumn column = excelTable.Columns[i];
                    if (excelColumnMatch.maybeColumnName.Contains(column.ColumnName))
                    {
                        keyValuePairs.Add(excelColumnMatch.columnName, i);
                        break;
                    }
                }
            }

            //检查字段是否匹配完全
            List<string> noMatchColumnNames = new List<string>();
            foreach (ExcelColumnMatch excelColumnMatch in excelColumnMatchs)
            {
                if (!keyValuePairs.ContainsKey(excelColumnMatch.columnName))
                {
                    noMatchColumnNames.Add(excelColumnMatch.columnName);
                }
            }
            if (noMatchColumnNames.Count > 0)
            {
                throw new Exception($"找不到字段{noMatchColumnNames.Round()}");
            }



            int rowE = 1;
            string columnE = EmptyString;
            try
            {
                //添加数据
                foreach (DataRow excelRow in excelTable.Rows)
                {
                    rowE++;
                    columnE = EmptyString;
                    DataRow newRow = table.Rows.Add();
                    foreach (ExcelColumnMatch excelColumnMatch in excelColumnMatchs)
                    {
                        columnE = excelColumnMatch.maybeColumnName.First();
                        var cell = excelRow[keyValuePairs[excelColumnMatch.columnName]];


                        if (excelColumnMatch.type == typeof(DateTime))
                        {
                            if (cell.GetType() == typeof(int) || cell.GetType() == typeof(double))
                            {
                                newRow[excelColumnMatch.columnName] = DateTime.FromOADate((double)cell);
                            }
                            else
                            {
                                newRow[excelColumnMatch.columnName] = DBNull.Value;
                            }
                        }
                        else if (excelColumnMatch.type == typeof(int))
                        {
                            if (cell is null || cell is DBNull)
                            {
                                newRow[excelColumnMatch.columnName] = 0;
                            }
                            else if (cell.GetType().IsInteger() || cell.GetType().IsDecimal())
                            {
                                newRow[excelColumnMatch.columnName] = Convert.ToInt32(cell);
                            }
                            else if (cell.GetType() == typeof(string))
                            {
                                newRow[excelColumnMatch.columnName] = cell?.ToString()?.StringToInt() ?? 0;
                            }
                            else
                            {
                                newRow[excelColumnMatch.columnName] = 0;
                            }
                        }
                        else if (excelColumnMatch.type == typeof(decimal))
                        {
                            if (cell is null || cell is DBNull)
                            {
                                newRow[excelColumnMatch.columnName] = 0;
                            }
                            else if (cell.GetType().IsInteger() || cell.GetType().IsDecimal())
                            {
                                newRow[excelColumnMatch.columnName] = Convert.ToDecimal(cell);
                            }
                            else if (cell.GetType() == typeof(string))
                            {
                                newRow[excelColumnMatch.columnName] = cell?.ToString()?.StringToDecimal() ?? 0m;
                            }
                            else
                            {
                                newRow[excelColumnMatch.columnName] = 0;
                            }
                        }
                        else if (excelColumnMatch.type == typeof(double))
                        {
                            if (cell is null || cell is DBNull)
                            {
                                newRow[excelColumnMatch.columnName] = 0;
                            }
                            else if (cell.GetType().IsInteger() || cell.GetType().IsDecimal())
                            {
                                newRow[excelColumnMatch.columnName] = Convert.ToDouble(cell);
                            }
                            else if (cell.GetType() == typeof(string))
                            {
                                newRow[excelColumnMatch.columnName] = cell?.ToString()?.StringToDouble() ?? 0.0;
                            }
                            else
                            {
                                newRow[excelColumnMatch.columnName] = 0.0;
                            }
                        }
                        else
                        {
                            newRow[excelColumnMatch.columnName] = cell;
                        }
                    }
                }
            }
            catch
            {
                throw new Exception($"错误数据：字段“{columnE}”，行号{rowE}；");

            }




        }

        #endregion

    }
}
